package com.alen.sun.dao;

import com.alen.sun.bean.Order;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper {

    //添加订单
    @Insert("INSERT INTO `order` (orderId, order_date, package_start, package_end,order_cancel_date,package_name,package_price, packageNum, total_amount, order_status, " +
            "elderlyId, packageId) VALUES (#{orderId},#{orderTime},#{packageStart},#{packageEnd},#{orderCancelTime},#{packageName},#{packagePrice},#{packageNum},#{totalAmount},#{orderStatus},#{elderlyId},#{packageId})")
    Boolean addOrder(Order order);

//    //修改代码
//    @Update("UPDATE order SET order_date = #{orderTime}, package_start = #{packageStart}, package_end = #{packageEnd}, " +
//            "package_price = #{packagePrice} , packageNum = #{packageNum}, total_amount = #{totalAmount}, order_status = #{orderStatus}, elderlyId = #{elderlyId}, " +
//            "packageId = #{packageId} WHERE orderId = #{orderId}")
//    Boolean updateOrder(Order order);

    //修改订单状态
    @Update("UPDATE `order` SET order_status = #{orderStatus} WHERE orderId = #{orderId}")
    Boolean updateOrder(Order order);

    //删除订单
    @Delete("DELETE FROM `order` WHERE orderId = #{orderId}")
    Boolean deleteOrder(Order order);

    /**
     * 查询总记录数
     * @return
     */
    @Select("select count(*) from `order`")
    Long count();

    /**
     * 分页查询,获取列表数据
     * @param start
     * @param pageSize
     * @return
     */
    @Select("select * from `order` limit #{start},#{pageSize}")
    List<Order> page(Integer start, Integer pageSize);

    //根据id查询
    @Select("SELECT count(*) FROM `order` WHERE orderId = #{orderId}")
    Integer selectById(String orderId);

    //批量删除
    @Delete({
            "<script>",
            "delete from `order` where orderId in",
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>#{id}</foreach>",
            "</script>"
    })
    Boolean deleteByIds(List<String> ids);

    /**
     * 非基础功能
     */

    //家属端根据自己的外键elderlyId在数据库查询出对应的订单记录信息,根据时间顺序,全部订单
    @Select("SELECT * FROM `order` WHERE elderlyId = #{elderlyId} ORDER BY order_date DESC limit #{start},#{pageSize}")
    List<Order> getOrderByElderlyIdAll(String elderlyId ,Integer start, Integer pageSize);

    @Select("select count(*) from `order` WHERE elderlyId = #{elderlyId}")
    Long countAll(String elderlyId);

    //家属端根据自己的外键elderlyId在数据库查询出对应的订单记录信息,根据时间顺序,未支付订单
    @Select("SELECT * FROM `order` WHERE elderlyId = #{elderlyId} and order_status = 0 ORDER BY order_date DESC limit #{start},#{pageSize}")
    List<Order> getOrderByElderlyIdUnpaid(String elderlyId,Integer start, Integer pageSize);

    @Select("select count(*) from `order` WHERE elderlyId = #{elderlyId} and order_status = 0")
    Long countUnpaid(String elderlyId);

    //家属端根据自己的外键elderlyId在数据库查询出对应的订单记录信息,根据时间顺序,已付款订单
    @Select("SELECT * FROM `order` WHERE elderlyId = #{elderlyId} and order_status = 1 ORDER BY order_date DESC limit #{start},#{pageSize}")
    List<Order> getOrderByElderlyIdPaid(String elderlyId,Integer start, Integer pageSize);

    @Select("select count(*) from `order` WHERE elderlyId = #{elderlyId} and order_status = 1")
    Long countPaid(String elderlyId);

    //查询所有未支付状态的订单,状态为0,条件,当前时间与订单取消时间对比
    @Select("SELECT * FROM `order` WHERE order_status = 0 and TIMESTAMPDIFF(SECOND, NOW(), order_cancel_date) < 0")
    List<Order> unpaidOrders();

    //数据id 修改订单状态为2
    @Update({
            "<script>",
            "UPDATE `order` SET order_status = 2 WHERE orderId IN",
            "<foreach item='orderId' collection='orderIdList' open='(' separator=',' close=')'>",
            "#{orderId}",
            "</foreach>",
            "</script>"
    })
    Boolean updateByIds(@Param("orderIdList") List<String> orderIdList);


    //网页搜索功能,聚合模糊查询,订单编号,套餐名
    @Select("SELECT * FROM `order` " +
            "WHERE orderId LIKE CONCAT('%', #{keywords}, '%') " +
            "OR package_name LIKE CONCAT('%', #{keywords}, '%') ")
    List<Order> searchByKeywords(String keywords);

    //数据分析可视化,订单金额,时间统计
    @Select("SELECT DATE(order_date) AS order_date, SUM(total_amount) AS day_amount FROM" +
            "`order`" +
            "GROUP BY DATE(order_date)")
    List<Map<String, Object>> getOrderTotalAmountAndTime();

    /**
     * 数据可视化分析,套餐截止日期
     * @return
     */
    @Select("SELECT elderly.`name`, `order`.package_name, DATEDIFF(package_end, NOW()) AS days_until_expiry FROM `order` INNER JOIN elderly ON `order`.elderlyId = elderly.elderlyId WHERE `order`.package_end >= NOW() AND package_end <= DATE_ADD(NOW(), INTERVAL 10 DAY) AND `order`.order_status = 1 ORDER BY TIMESTAMPDIFF(SECOND, NOW(), package_end)")
    List<Map<String, Object>> getDaysUntil();
}
